import sqlite3

# 连接到Django项目的SQLite数据库（替换为你的数据库路径）
conn = sqlite3.connect('../../db.sqlite3')
cursor = conn.cursor()

try:
    # 新增：权限表（rbac_permission）数据
    permission_sql = """
    INSERT INTO rbac_permission (id, title, url, name, menu_id, pid) VALUES
    (1, '用户列表', '/stark/house/user/list/', 'house_user_list', NULL, NULL),
    (2, '角色添加', '/stark/house/user/add/', 'house_user_add', NULL, 1),
    (3, '修改用户', '/stark/house/user/change/(?P<pk>\\d+)/', 'house_user_change', NULL, 1),
    (4, '删除用户', '/stark/house/user/delete/(?P<pk>\\d+)/', 'house_user_delete', NULL, 1),
    (5, '城市列表', '/stark/house/cityarea/list/', 'house_cityarea_list', NULL, NULL),
    (6, '添加城市', '/stark/house/cityarea/add/', 'house_cityarea_add', NULL, 5),
    (7, '编辑城市', '/stark/house/cityarea/change/(?P<pk>\\d+)/', 'house_cityarea_change', NULL, 5),
    (8, '删除城市', '/stark/house/cityarea/delete/(?P<pk>\\d+)/', 'house_cityarea_delete', NULL, 5),
    (9, '角色列表', '/rbac/role/list/', 'rbac:role_list', NULL, NULL),
    (10, '添加角色', '/rbac/role/add/', 'rbac:role_add', NULL, 9),
    (11, '编辑角色', '/rbac/role/edit/(?P<pk>\\d+)/', 'rbac:role_edit', NULL, 9),
    (12, '删除角色', '/rbac/role/del/(?P<pk>\\d+)/', 'rbac:role_del', NULL, 9),
    (13, '菜单列表', '/rbac/menu/list/', 'rbac:menu_list', NULL, NULL),
    (14, '添加菜单', '/rbac/menu/add/', 'rbac:menu_add', NULL, 13),
    (15, '编辑菜单', '/rbac/menu/edit/(?P<pk>\\d+)/', 'rbac:menu_edit', NULL, 13),
    (16, '删除菜单', '/rbac/menu/del/(?P<pk>\\d+)/', 'rbac:menu_del', NULL, 13),
    (17, '添加二级菜单', '/rbac/second/menu/add/(?P<menu_id>\\d+)', 'rbac:second_menu_add', NULL, 13),
    (18, '编辑二级菜单', '/rbac/second/menu/edit/(?P<pk>\\d+)/', 'rbac:second_menu_edit', NULL, 13),
    (19, '删除二级菜单', '/rbac/second/menu/del/(?P<pk>\\d+)/', 'rbac:second_menu_del', NULL, 13),
    (20, '添加权限', '/rbac/permission/add/(?P<second_menu_id>\\d+)/', 'rbac:permission_add', NULL, 13),
    (21, '编辑权限', '/rbac/permission/edit/(?P<pk>\\d+)/', 'rbac:permission_edit', NULL, 13),
    (22, '删除权限', '/rbac/permission/del/(?P<pk>\\d+)/', 'rbac:permission_del', NULL, 13),
    (23, '批量操作权限', '/rbac/multi/permissions/', 'rbac:multi_permissions', NULL, 13),
    (24, '批量删除权限', '/rbac/multi/permissions/del/(?P<pk>\\d+)/', 'rbac:multi_permissions_del', NULL, 23),
    (25, '分配权限', '/rbac/distribute/permissions/', 'rbac:distribute_permissions', NULL, 13),
    (26, '房源列表', '/stark/house/house/list/', 'house_house_list', NULL, NULL),
    (27, '添加房源', '/stark/house/house/add/', 'house_house_add', NULL, 26),
    (28, '修改房源', '/stark/house/house/change/(?P<pk>\\d+)/', 'house_house_change', NULL, 26),
    (29, '删除房源', '/stark/house/house/delete/(?P<pk>\\d+)/', 'house_house_delete', NULL, 26),
    (30, '租赁业务列表', '/stark/house/contract/list/', 'house_contract_list', NULL, NULL),
    (31, '添加租赁业务', '/stark/house/contract/add/', 'house_contract_add', NULL, 30),
    (32, '编辑租赁业务', '/stark/house/contract/change/(?P<pk>\\d+)/', 'house_contract_change', NULL, 30),
    (33, '删除租赁业务', '/stark/house/contract/delete/(?P<pk>\\d+)/', 'house_contract_delete', NULL, 30),
    (34, '租金列表', '/stark/house/reminder/list/', 'house_reminder_list', NULL, NULL),
    (35, '添加租金', '/stark/house/reminder/add/', 'house_reminder_add', NULL, 34),
    (36, '编辑租金', '/stark/house/reminder/change/(?P<pk>\\d+)/', 'house_reminder_change', NULL, 34),
    (37, '删除租金', '/stark/house/reminder/delete/(?P<pk>\\d+)/', 'house_reminder_delete', NULL, 34),
    (38, '抄表记录列表', '/stark/house/meterreading/list/', 'house_meterreading_list', NULL, NULL),
    (39, '添加抄表记录', '/stark/house/meterreading/add/', 'house_meterreading_add', NULL, 38),
    (40, '编辑抄表记录', '/stark/house/meterreading/change/(?P<pk>\\d+)/', 'house_meterreading_change', NULL, 38),
    (41, '删除抄表记录', '/stark/house/meterreading/delete/(?P<pk>\\d+)/', 'house_meterreading_delete', NULL, 38),
    (42, '提醒列表', '/stark/house/rentbill/list/', 'house_rentbill_list', NULL, NULL),
    (43, '添加提醒', '/stark/house/rentbill/add/', 'house_rentbill_add', NULL, 42),
    (44, '编辑提醒', '/stark/house/rentbill/change/(?P<pk>\\d+)/', 'house_rentbill_change', NULL, 42),
    (45, '删除提醒', '/stark/house/rentbill/delete/(?P<pk>\\d+)/', 'house_rentbill_delete', NULL, 42);
    """
    cursor.execute(permission_sql)
    print("rbac_permission表数据插入成功")
    
    # 1. 城市/区域表（CityArea）
    city_area_sql = """
    INSERT INTO house_cityarea (city_name, area_name) VALUES
    ('北京', '朝阳区'),
    ('北京', '海淀区'),
    ('上海', '浦东新区'),
    ('上海', '静安区'),
    ('广州', '天河区'),
    ('广州', '越秀区'),
    ('深圳', '南山区'),
    ('深圳', '福田区'),
    ('成都', '锦江区'),
    ('成都', '武侯区');
    """
    cursor.execute(city_area_sql)
    print("CityArea表数据插入成功")

    # 2. 用户信息表（User）- 继承自RbacUserInfo，包含基础字段+phone
    user_sql = """
    INSERT INTO house_user (username, name, password, email, phone, identity_type) VALUES
    ('zhangsan', '张三', 'pbkdf2_sha256$...', 'zhangsan@example.com', '13800138001', '2'),
    ('lisi', '李四', 'pbkdf2_sha256$...', 'lisi@example.com', '13800138002', '3'),
    ('wangwu', '王五', 'pbkdf2_sha256$...', 'wangwu@example.com', '13800138003', '1'),
    ('zhaoliu', '赵六', 'pbkdf2_sha256$...', 'zhaoliu@example.com', '13800138004', '2'),
    ('sunqi', '孙七', 'pbkdf2_sha256$...', 'sunqi@example.com', '13800138005', '3'),
    ('zhouba', '周八', 'pbkdf2_sha256$...', 'zhouba@example.com', '13800138006', '1'),
    ('wujiu', '吴九', 'pbkdf2_sha256$...', 'wujiu@example.com', '13800138007', '2'),
    ('zhengshi', '郑十', 'pbkdf2_sha256$...', 'zhengshi@example.com', '13800138008', '3'),
    ('qianshiyi', '钱十一', 'pbkdf2_sha256$...', 'qianshiyi@example.com', '13800138009', '1'),
    ('sunshi2', '孙十二', 'pbkdf2_sha256$...', 'sunshi2@example.com', '13800138010', '2');
    """
    cursor.execute(user_sql)
    print("User表数据插入成功")

    # 3. 房源表（House）- 外键为landlord_id（关联User）、city_area_id（关联CityArea）
    house_sql = """
    INSERT INTO house_house (landlord_id_id, city_area_id_id, address, house_type, area_size, rent_price, deposit, facilities, status, description, images) VALUES
    (1, 1, '朝阳区建国路88号', '三室一厅', '120㎡', 8000.00, 16000.00, '空调,冰箱,洗衣机', '1', '采光好，交通便利', 'image1.jpg'),
    (4, 2, '海淀区中关村大街1号', '两室一厅', '90㎡', 6500.00, 13000.00, '空调,电视,洗衣机', '1', '靠近地铁，周边配套全', 'image2.jpg'),
    (7, 3, '浦东新区张江路100号', '一室一厅', '60㎡', 5000.00, 10000.00, '冰箱,洗衣机', '1', '精装，拎包入住', 'image3.jpg'),
    (1, 4, '静安区南京西路123号', '三室两厅', '130㎡', 9000.00, 18000.00, '空调,冰箱,电视,洗衣机', '1', '市中心，繁华地段', 'image4.jpg'),
    (4, 5, '天河区天河路222号', '两室两厅', '100㎡', 7000.00, 14000.00, '空调,冰箱,洗衣机', '1', '小区环境好，物业佳', 'image5.jpg'),
    (7, 6, '越秀区北京路333号', '一室一厅', '55㎡', 4500.00, 9000.00, '洗衣机', '1', '老城区，生活方便', 'image6.jpg'),
    (10, 7, '南山区科技园路55号', '三室一厅', '110㎡', 8500.00, 17000.00, '空调,冰箱,电视', '1', '近公司，适合上班族', 'image7.jpg'),
    (1, 8, '福田区深南大道666号', '两室一厅', '85㎡', 6000.00, 12000.00, '空调,洗衣机', '1', '交通便利，商圈多', 'image8.jpg'),
    (4, 9, '锦江区春熙路77号', '三室两厅', '125㎡', 8200.00, 16400.00, '空调,冰箱,电视,洗衣机', '1', '市中心，购物方便', 'image9.jpg'),
    (7, 10, '武侯区武侯大道88号', '两室一厅', '95㎡', 6800.00, 13600.00, '空调,冰箱', '1', '小区安静，适合居住', 'image10.jpg');
    """
    cursor.execute(house_sql)
    print("House表数据插入成功")

    # 4. 租赁合同表（Contract）- 外键为house_id、tenant_id、landlord_id（均关联User）
    contract_sql = """
    INSERT INTO house_contract (house_id_id, tenant_id_id, landlord_id_id, start_date, end_date, rent_cycle, total_rent, status) VALUES
    (1, 2, 1, '2025-01-01', '2026-01-01', '月付', 96000.00, '1'),
    (2, 5, 4, '2025-02-01', '2026-02-01', '月付', 78000.00, '1'),
    (3, 8, 7, '2025-03-01', '2026-03-01', '月付', 60000.00, '1'),
    (4, 2, 1, '2025-04-01', '2026-04-01', '季付', 108000.00, '1'),
    (5, 5, 4, '2025-05-01', '2026-05-01', '季付', 84000.00, '1'),
    (6, 8, 7, '2025-06-01', '2026-06-01', '月付', 54000.00, '1'),
    (7, 2, 10, '2025-07-01', '2026-07-01', '月付', 102000.00, '1'),
    (8, 5, 1, '2025-08-01', '2026-08-01', '月付', 72000.00, '1'),
    (9, 8, 4, '2025-09-01', '2026-09-01', '季付', 98400.00, '1'),
    (10, 2, 7, '2025-10-01', '2026-10-01', '月付', 81600.00, '1');
    """
    cursor.execute(contract_sql)
    print("Contract表数据插入成功")

    # 5. 租金账单表（RentBill）- 外键为contract_id（关联Contract）
    rent_bill_sql = """
    INSERT INTO house_rentbill (contract_id_id, bill_month, rent_amount, paid_status, paid_date) VALUES
    (1, '2025-01', 8000.00, '1', NULL),
    (1, '2025-02', 8000.00, '1', NULL),
    (2, '2025-02', 6500.00, '1', NULL),
    (2, '2025-03', 6500.00, '1', NULL),
    (3, '2025-03', 5000.00, '1', NULL),
    (4, '2025-04', 9000.00, '1', NULL),
    (5, '2025-05', 7000.00, '1', NULL),
    (6, '2025-06', 4500.00, '1', NULL),
    (7, '2025-07', 8500.00, '1', NULL),
    (8, '2025-08', 6000.00, '1', NULL);
    """
    cursor.execute(rent_bill_sql)
    print("RentBill表数据插入成功")

    # 6. 抄表记录（MeterReading）- 外键为house（关联House，字段名为house_id）
    meter_reading_sql = """
    INSERT INTO house_meterreading (house_id, meter_type, reading_date, previous_reading, current_reading) VALUES
    (1, '1', '2025-01-01', 100.00, 150.00),
    (1, '2', '2025-01-01', 50.00, 70.00),
    (2, '1', '2025-02-01', 200.00, 230.00),
    (2, '2', '2025-02-01', 80.00, 95.00),
    (3, '1', '2025-03-01', 150.00, 180.00),
    (3, '2', '2025-03-01', 60.00, 75.00),
    (4, '1', '2025-04-01', 300.00, 350.00),
    (4, '2', '2025-04-01', 100.00, 120.00),
    (5, '1', '2025-05-01', 250.00, 280.00),
    (5, '2', '2025-05-01', 90.00, 105.00);
    """
    cursor.execute(meter_reading_sql)
    print("MeterReading表数据插入成功")

    # 7. 提醒表（Reminder）
    reminder_sql = """
    INSERT INTO house_reminder (reminder_type, reminder_content, remind_date, is_processed) VALUES
    ('1', '合同ID为1即将到期', '2025-12-01', '1'),
    ('2', '账单ID为1租金待收', '2025-01-05', '1'),
    ('1', '合同ID为2即将到期', '2025-01-01', '2'),
    ('2', '账单ID为2租金待收', '2025-02-05', '1'),
    ('1', '合同ID为3即将到期', '2025-02-01', '1'),
    ('2', '账单ID为3租金待收', '2025-03-05', '1'),
    ('1', '合同ID为4即将到期', '2025-03-01', '1'),
    ('2', '账单ID为4租金待收', '2025-04-05', '1'),
    ('1', '合同ID为5即将到期', '2025-04-01', '1'),
    ('2', '账单ID为5租金待收', '2025-05-05', '1');
    """
    cursor.execute(reminder_sql)
    print("Reminder表数据插入成功")

    # 提交事务
    conn.commit()
    print("所有表数据插入完成！")

except Exception as e:
    # 出错时回滚
    conn.rollback()
    print(f"插入失败：{str(e)}")

finally:
    # 关闭连接
    conn.close()